const db = require('../db');

class ChatroomModel {
    // 获取所有聊天室信息（包含当前播放的歌曲信息）
    static async getAllChatrooms() {
        const sql = `
            SELECT 
                c.roomID,
                c.img,
                c.title,
                c.intro,
                c.maxUsersLength,
                c.isPlaying,
                s.ID as songID,
                s.name as songName,
                s.time as songTime,
                s.author as songAuthor,
                s.picUrl as songPicUrl
            FROM 
                chatroom c
            LEFT JOIN 
                song s ON c.songID = s.ID
        `;

        try {
            const [rows] = await db.query(sql);
            return rows || []; // 确保返回数组
        } catch (error) {
            console.error('获取聊天室列表失败:', error);
            throw error;
        }
    }

    // 获取单个聊天室信息
    static async getChatroomById(roomID) {
        const sql = `
            SELECT 
                c.roomID,
                c.img,
                c.title,
                c.intro,
                c.maxUsersLength,
                c.isPlaying,
                s.ID as songID,
                s.name as songName,
                s.time as songTime,
                s.author as songAuthor,
                s.picUrl as songPicUrl
            FROM 
                chatroom c
            LEFT JOIN 
                song s ON c.songID = s.ID
            WHERE 
                c.roomID = ?
        `;

        try {
            const [rows] = await db.query(sql, [roomID]);
            return rows[0] || null;
        } catch (error) {
            console.error('获取聊天室信息失败:', error);
            throw error;
        }
    }

    // 获取聊天室的歌曲列表
    static async getChatroomSongs(roomID) {
        const sql = `
            SELECT 
                s.ID as songID,
                s.name as songName,
                s.time as songTime,
                s.author as songAuthor,
                s.picUrl as songPicUrl
            FROM 
                chatroom_song cs
            JOIN 
                song s ON cs.songID = s.ID
            WHERE 
                cs.roomID = ?
            ORDER BY 
                cs.id ASC
        `;

        try {
            const [rows] = await db.query(sql, [roomID]);
            return rows || []; // 确保返回数组
        } catch (error) {
            console.error('获取聊天室歌曲列表失败:', error);
            throw error;
        }
    }

    // 添加歌曲到聊天室
    static async addSongToChatroom(roomId, song) {
        const connection = await db.getConnection();

        try {
            await connection.beginTransaction();

            // 检查歌曲是否存在于 song 表中
            const [existingSong] = await connection.query(
                'SELECT ID FROM song WHERE ID = ?',
                [song.id]
            );

            // 如果歌曲不存在，则添加到 song 表
            if (existingSong.length === 0) {
                await connection.query(
                    'INSERT INTO song (ID, name, time, author, picUrl) VALUES (?, ?, ?, ?, ?)',
                    [song.id, song.name, song.time || null, song.author, song.picUrl || null]
                );
                console.log(`添加新歌曲: ${song.name}`);
            }

            // 检查歌曲是否已经添加到聊天室
            const [existingChatroomSong] = await connection.query(
                'SELECT id FROM chatroom_song WHERE roomID = ? AND songID = ?',
                [roomId, song.id]
            );

            // 如果歌曲尚未添加到聊天室，则添加
            if (existingChatroomSong.length === 0) {
                await connection.query(
                    'INSERT INTO chatroom_song (roomID, songID) VALUES (?, ?)',
                    [roomId, song.id]
                );
                console.log(`将歌曲 ${song.name} 添加到聊天室 ${roomId}`);
            }

            await connection.commit();

            return {
                roomId,
                song: {
                    id: song.id,
                    name: song.name,
                    time: song.time,
                    author: song.author,
                    picUrl: song.picUrl
                }
            };
        } catch (error) {
            await connection.rollback();
            console.error('添加歌曲到聊天室失败:', error);
            throw error;
        } finally {
            connection.release();
        }
    }
}

module.exports = ChatroomModel; 